﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
using Xceed.Wpf.Toolkit;

namespace ProjectEF6
{
    class DB
    {
        private static string connection = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
        private static SqlConnection myConnection = new SqlConnection(connection);

        public DB()
        {

        }

        public List<reservations> getAllReservations()
        {
            try
            {
                string sqlComm = @"SELECT TOP 1000 [Reservation].[Id],
                            [Company].[Name],
                            [Reservation].[StartDate],
                            [Reservation].[EndDate],
                            [Reservation].[CompanyId],
                            [Location].[Id],
                            [Location].[Name] as locationName,
                            [Reservation].[CreateDate]
                            FROM [Pj].[dbo].[Reservation]
                            INNER JOIN [Pj].[dbo].[Company] on Reservation.CompanyId = Company.Id
                            INNER JOIN [Pj].[dbo].[Location] on Reservation.LocationId = Location.Id
                            ORDER BY [Reservation].[StartDate]";
                            
                SqlCommand comm = new SqlCommand(sqlComm, myConnection);
                myConnection.Open();
                SqlDataReader reader = comm.ExecuteReader();
                reader.Read();
                List<reservations> reservaties = new List<reservations>();
                do
                {
                    reservations r = new reservations();
                    for (int i = 0; i <= reader.FieldCount; i++)
                    {
                        r.Id = reader.GetInt32(0);
                        r.Name = reader.GetString(1);
                        r.StartDate = reader.GetDateTime(2);
                        r.EndDate = reader.GetDateTime(3);
                        r.CompanyId = reader.GetInt32(4);
                        r.locationId = reader.GetInt32(5);
                        r.locationName = reader.GetString(6);
                        r.CreateDate = reader.GetDateTime(7);
                    }
                    reservaties.Add(r);
                } while (reader.Read());
                return reservaties;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;
              
            }
            finally
            {
                myConnection.Close();
            }
        }

        public List<Location> getAllLocations()
        {
            try
            {
                string sqlComm = @"SELECT [Id],[Name] FROM [Pj].[dbo].[Location]";
                SqlCommand comm = new SqlCommand(sqlComm, myConnection);
                myConnection.Open();
                SqlDataReader reader = comm.ExecuteReader();
                reader.Read();
                List<Location> locaties = new List<Location>();
                do
                {
                    Location l = new Location();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        l.Id = reader.GetInt32(0);
                        l.Name = reader.GetString(1);

                    }
                    locaties.Add(l);
                } while (reader.Read());
                return locaties;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;

            }
            finally
            {
                myConnection.Close();
            }
        }

        public List<Company> getAllCompanies()
        {
            try
            {
                string sqlComm = @"SELECT [Company].[Id]
                                  ,[Company].[Name]
                                  ,[Company].[Street]
                                  ,[Company].[Zipcode]
                                  ,[Company].[City]
                                  ,[Company].[Country]
                                  ,[Company].[Email]
                                  ,[Company].[Phone]
                                  ,[Company].[Employees]
                              FROM [Pj].[dbo].[Company]";
                SqlCommand comm = new SqlCommand(sqlComm, myConnection);
                myConnection.Open();
                SqlDataReader reader = comm.ExecuteReader();
                reader.Read();
                List<Company> companies = new List<Company>();
                do
                {
                    Company c = new Company();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        c.Id = reader.GetInt32(0);
                        c.Name = reader.GetString(1);
                        c.Street = reader.GetString(2);
                        c.Zipcode = reader.GetInt32(3);
                        c.City = reader.GetString(4);
                        c.Country = reader.GetString(5);
                        c.Email = reader.GetString(6);
                        c.Phone = reader.GetInt32(7);
                        c.Employees = reader.GetInt32(8);
                    }
                    companies.Add(c);
                } while (reader.Read());
                return companies;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;

            }
            finally
            {
                myConnection.Close();
            }
        }

        public List<Contract> getAllContracts()
        {
            try
            {
                string sqlComm = @"SELECT [Id]
                                  ,[StartDate]
                                  ,[EndDate]
                                  ,[CompanyId]
                                  ,[ContractFormulaId]
                              FROM [Pj].[dbo].[Contract]";
                SqlCommand comm = new SqlCommand(sqlComm, myConnection);
                myConnection.Open();
                SqlDataReader reader = comm.ExecuteReader();
                reader.Read();
                List<Contract> contracts = new List<Contract>();
                do
                {
                    Contract c = new Contract();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        c.Id = reader.GetInt32(0);
                        c.StartDate = reader.GetDateTime(1);
                        c.EndDate = reader.GetDateTime(2);
                        c.CompanyId = reader.GetInt32(3);
                        c.ContractFormulaId = reader.GetInt32(4);
                    }
                    contracts.Add(c);
                } while (reader.Read());
                return contracts;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;

            }
            finally
            {
                myConnection.Close();
            }
        }

        public List<ContractFormula> getAllContractFormulas()
        {
            try
            {
                string sqlComm = @"SELECT [Id]
                                  ,[Description]
                                  ,[MaxUsageHoursPerPeriod]
                                  ,[PeriodInMonths]
                                  ,[NoticePeriodInMonths]
                                  ,[Price]
                              FROM [Pj].[dbo].[ContractFormula]";
                SqlCommand comm = new SqlCommand(sqlComm, myConnection);
                myConnection.Open();
                SqlDataReader reader = comm.ExecuteReader();
                reader.Read();
                List<ContractFormula> contractFormula = new List<ContractFormula>();
                do
                {
                    ContractFormula c = new ContractFormula();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        c.Id = reader.GetInt32(0);
                        c.Description = reader.GetString(1);
                        c.MaxUsageHoursPerPeriod = reader.GetInt32(2);
                        c.PeriodInMonths = reader.GetInt32(3);
                        c.NoticePeriodInMonths = reader.GetInt32(4);
                        c.Price = reader.GetString(5);
                    }
                    contractFormula.Add(c);
                } while (reader.Read());
                return contractFormula;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;

            }
            finally
            {
                myConnection.Close();
            }
        }

        public List<reservations> getAllReservationsOnly(int location)
        {
            try
            {
                string sqlComm = @"SELECT [Id]
                                  ,[StartDate]
                                  ,[EndDate]
                                  ,[CompanyId]
                                  ,[LocationId]
                                  ,[CreateDate]
                              FROM [Pj].[dbo].[Reservation]
                              WHERE LocationId = "+location;
                SqlCommand comm = new SqlCommand(sqlComm, myConnection);
                myConnection.Open();
                SqlDataReader reader = comm.ExecuteReader();
                reader.Read();
                List<reservations> reservatie = new List<reservations>();
                do
                {
                    reservations r = new reservations();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        r.Id = reader.GetInt32(0);
                        r.StartDate = reader.GetDateTime(1);
                        r.EndDate = reader.GetDateTime(2);
                        r.CompanyId = reader.GetInt32(3);
                        r.locationId = reader.GetInt32(4);
                        r.CreateDate = reader.GetDateTime(5);
                    }
                    reservatie.Add(r);
                } while (reader.Read());
                return reservatie;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;

            }
            finally
            {
                myConnection.Close();
            }
        }

        public List<reservations> getAllReservationsOnly(int location, int withoutId)
        {
            try
            {
                string sqlComm = @"SELECT [Id]
                                  ,[StartDate]
                                  ,[EndDate]
                                  ,[CompanyId]
                                  ,[LocationId]
                                  ,[CreateDate]
                              FROM [Pj].[dbo].[Reservation]
                              WHERE Id <> "+withoutId +
                              "AND LocationId = "+location;
                SqlCommand comm = new SqlCommand(sqlComm, myConnection);
                myConnection.Open();
                SqlDataReader reader = comm.ExecuteReader();
                reader.Read();
                List<reservations> reservatie = new List<reservations>();
                do
                {
                    reservations r = new reservations();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        r.Id = reader.GetInt32(0);
                        r.StartDate = reader.GetDateTime(1);
                        r.EndDate = reader.GetDateTime(2);
                        r.CompanyId = reader.GetInt32(3);
                        r.locationId = reader.GetInt32(4);
                        r.CreateDate = reader.GetDateTime(5);
                    }
                    reservatie.Add(r);
                } while (reader.Read());
                return reservatie;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;

            }
            finally
            {
                myConnection.Close();
            }
        }

        public List<reservations> getReservationsFromDay(DateTime startSelected, DateTime endSelected)
        {
            try
            {
                string sqlComm = @"SELECT TOP 1000 [Reservation].[Id],
                            [Company].[Name],
                            [Reservation].[StartDate],
                            [Reservation].[EndDate],
                            [Reservation].[CompanyId],
                            [Location].[Name] as locationName,
                            [Reservation].[CreateDate]
                            FROM [Pj].[dbo].[Reservation]
                            INNER JOIN [Pj].[dbo].[Company] on Reservation.CompanyId = Company.Id
                            INNER JOIN [Pj].[dbo].[Location] on Reservation.LocationId = Location.Id
                            WHERE [Reservation].[StartDate] >  CONVERT(DateTime,'" + startSelected + "',103) AND [Reservation].[StartDate] < CONVERT(DateTime,'" + endSelected + "',103)";
                SqlCommand comm = new SqlCommand(sqlComm, myConnection);
                myConnection.Open();
                SqlDataReader reader = comm.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    List<reservations> reservaties = new List<reservations>();
                    do
                    {
                        reservations r = new reservations();
                        for (int i = 0; i <= reader.FieldCount; i++)
                        {
                            r.Id = reader.GetInt32(0);
                            r.Name = reader.GetString(1);
                            r.StartDate = reader.GetDateTime(2);
                            r.EndDate = reader.GetDateTime(3);
                            r.CompanyId = reader.GetInt32(4);
                            r.locationName = reader.GetString(5);
                            r.CreateDate = reader.GetDateTime(6);
                        }
                        reservaties.Add(r);
                    } while (reader.Read());
                    return reservaties;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                
                return null;

            }
            finally
            {
                myConnection.Close();
            }
        }

        public List<Contract> getContractCompany(int companyId)
        {
            try
            {
                string sqlComm = @"SELECT [Contract].[Id]
                                          ,[Contract].[StartDate]
                                          ,[Contract].[EndDate]
	                                      ,[ContractFormula].[MaxUsageHoursPerPeriod]
                                          ,[Contract].[CompanyId]
                                          ,[Contract].[ContractFormulaId]
                                      FROM [Pj].[dbo].[Contract]
                                      INNER JOIN [Pj].[dbo].[ContractFormula] on [Contract].ContractFormulaId = ContractFormula.Id
                                      WHERE [Contract].[CompanyId] = "+companyId+" AND [StartDate] < CURRENT_TIMESTAMP AND [EndDate] > CURRENT_TIMESTAMP";
                SqlCommand comm = new SqlCommand(sqlComm, myConnection);
                myConnection.Open();
                SqlDataReader reader = comm.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    List<Contract> contractCompany = new List<Contract>();
                    do
                    {
                        Contract r = new Contract();
                        for (int i = 0; i <= reader.FieldCount; i++)
                        {
                            r.Id = reader.GetInt32(0);
                            r.StartDate = reader.GetDateTime(1);
                            r.EndDate = reader.GetDateTime(2);
                            r.MaxUsageHoursPerPeriod = reader.GetInt32(3);
                            r.CompanyId = reader.GetInt32(4);
                            r.ContractFormulaId = reader.GetInt32(5);
                        }
                        contractCompany.Add(r);
                    } while (reader.Read());
                    return contractCompany;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

                return null;

            }
            finally
            {
                myConnection.Close();
            }
        }

        public List<Contract> getRunningContracts()
        {
            try
            {
                string sqlComm = @"SELECT [Id]
                                  ,[StartDate]
                                  ,[EndDate]
                                  ,[CompanyId]
                                  ,[ContractFormulaId]
                              FROM [Pj].[dbo].[Contract]
                                  WHERE [StartDate] < CURRENT_TIMESTAMP
                                  AND [EndDate] > CURRENT_TIMESTAMP";
                SqlCommand comm = new SqlCommand(sqlComm, myConnection);
                myConnection.Open();
                SqlDataReader reader = comm.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    List<Contract> contracts = new List<Contract>();
                    do
                    {
                        Contract r = new Contract();
                        for (int i = 0; i <= reader.FieldCount; i++)
                        {
                            r.Id = reader.GetInt32(0);
                            r.StartDate = reader.GetDateTime(1);
                            r.EndDate = reader.GetDateTime(2);
                            r.CompanyId = reader.GetInt32(3);
                            r.ContractFormulaId = reader.GetInt32(4);
                        }
                        contracts.Add(r);
                    } while (reader.Read());
                    return contracts;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

                return null;

            }
            finally
            {
                myConnection.Close();
            }
        }

        public List<reservations> getReservationsCompany(int companyId)
        {
            try
            {
                string sqlComm = @"SELECT [Id]
                                  ,[StartDate]
                                  ,[EndDate]
                                  ,[CompanyId]
                                  ,[LocationId]
                                  ,[CreateDate]
                              FROM [Pj].[dbo].[Reservation]
                              WHERE CompanyId = "+companyId;
                SqlCommand comm = new SqlCommand(sqlComm, myConnection);
                myConnection.Open();
                SqlDataReader reader = comm.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    List<reservations> reservationsCompany = new List<reservations>();
                    do
                    {
                        reservations r = new reservations();
                        for (int i = 0; i <= reader.FieldCount; i++)
                        {
                            r.Id = reader.GetInt32(0);
                            r.StartDate = reader.GetDateTime(1);
                            r.EndDate = reader.GetDateTime(2);
                            r.CompanyId = reader.GetInt32(3);
                            r.locationId = reader.GetInt32(4);
                            r.CreateDate = reader.GetDateTime(5);
                        }
                        reservationsCompany.Add(r);
                    } while (reader.Read());
                    return reservationsCompany;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

                return null;

            }
            finally
            {
                myConnection.Close();
            }
        }

        public List<reservations> getReservationsCompany(int companyId, int withoutId)
        {
            try
            {
                string sqlComm = @"SELECT [Id]
                                  ,[StartDate]
                                  ,[EndDate]
                                  ,[CompanyId]
                                  ,[LocationId]
                                  ,[CreateDate]
                              FROM [Pj].[dbo].[Reservation]
                              WHERE CompanyId = " + companyId +
                              "AND Id <> "+withoutId;
                SqlCommand comm = new SqlCommand(sqlComm, myConnection);
                myConnection.Open();
                SqlDataReader reader = comm.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    List<reservations> reservationsCompany = new List<reservations>();
                    do
                    {
                        reservations r = new reservations();
                        for (int i = 0; i <= reader.FieldCount; i++)
                        {
                            r.Id = reader.GetInt32(0);
                            r.StartDate = reader.GetDateTime(1);
                            r.EndDate = reader.GetDateTime(2);
                            r.CompanyId = reader.GetInt32(3);
                            r.locationId = reader.GetInt32(4);
                            r.CreateDate = reader.GetDateTime(5);
                        }
                        reservationsCompany.Add(r);
                    } while (reader.Read());
                    return reservationsCompany;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

                return null;

            }
            finally
            {
                myConnection.Close();
            }
        }

        public List<Contract> getContractsCompany(int companyId)
        {
            try
            {
                string sqlComm = @"SELECT [Id]
                                  ,[StartDate]
                                  ,[EndDate]
                                  ,[CompanyId]
                                  ,[ContractFormulaId]
                              FROM [Pj].[dbo].[Contract]
                                WHERE CompanyId = "+companyId;
                SqlCommand comm = new SqlCommand(sqlComm, myConnection);
                myConnection.Open();
                SqlDataReader reader = comm.ExecuteReader();
                reader.Read();
                List<Contract> contracts = new List<Contract>();
                do
                {
                    Contract c = new Contract();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        c.Id = reader.GetInt32(0);
                        c.StartDate = reader.GetDateTime(1);
                        c.EndDate = reader.GetDateTime(2);
                        c.CompanyId = reader.GetInt32(3);
                        c.ContractFormulaId = reader.GetInt32(4);
                    }
                    contracts.Add(c);
                } while (reader.Read());
                return contracts;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;

            }
            finally
            {
                myConnection.Close();
            }
        }

        public void newReservation(int locationId, DateTime start, DateTime end, int companyId)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connection))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("INSERT INTO Reservation(StartDate, EndDate, CompanyId, LocationId, CreateDate) VALUES(@StartDate, @EndDate, @CompanyId, @LocationId, @CreateDate)", conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add(new SqlParameter("@StartDate", start));
                    cmd.Parameters.Add(new SqlParameter("@EndDate", end));
                    cmd.Parameters.Add(new SqlParameter("@CompanyId", companyId));
                    cmd.Parameters.Add(new SqlParameter("@LocationId", locationId));
                    cmd.Parameters.Add(new SqlParameter("@CreateDate", DateTime.Now));
                    cmd.Connection = conn;
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        System.Windows.MessageBox.Show("Reservatie succesvol toegevoegd!");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);              

            }
        }
      
        public void deleteReservation(int id)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connection))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("DELETE FROM Reservation WHERE Id = @id", conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add(new SqlParameter("@id", id));
                    cmd.Connection = conn;
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        System.Windows.MessageBox.Show("Reservatie succesvol verwijderd!");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

            }
        }

        public Boolean editReservation(int id, DateTime start, DateTime end, int companyId, int locationId)
        {
            Boolean b = false;
            try
            {
                using (SqlConnection conn = new SqlConnection(connection))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("UPDATE Reservation SET StartDate=@start,EndDate=@end, CompanyId=@companyid, LocationId=@locationid WHERE Id=@id", conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add(new SqlParameter("@id", id));
                    cmd.Parameters.Add(new SqlParameter("@start", start));
                    cmd.Parameters.Add(new SqlParameter("@end", end));
                    cmd.Parameters.Add(new SqlParameter("@companyid", companyId));
                    cmd.Parameters.Add(new SqlParameter("@locationid", locationId));
                    cmd.Connection = conn;
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        System.Windows.MessageBox.Show("Reservatie succesvol veranderd!");
                        b = true;
                    }
                    else
                    {
                        b = false;
                    }
                }
            }
            
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

            }
            return b;
        }

        public void deleteContract(int id)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connection))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("DELETE FROM Contract WHERE Id = @id", conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add(new SqlParameter("@id", id));
                    cmd.Connection = conn;
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        System.Windows.MessageBox.Show("Contract succesvol stopgezet!");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

            }
        }

        public void newCompany(String Name, String Street, int Zipcode, String City, String Country, String Email, int Phone, int Employees)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connection))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("INSERT INTO Company(Name, Street, Zipcode, City, Country, Email, Phone, Employees) VALUES(@Name, @Street, @Zipcode, @City, @Country, @Email, @Phone, @Employees)", conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add(new SqlParameter("@Name", Name));
                    cmd.Parameters.Add(new SqlParameter("@Street", Street));
                    cmd.Parameters.Add(new SqlParameter("@Zipcode", Zipcode));
                    cmd.Parameters.Add(new SqlParameter("@City", City));
                    cmd.Parameters.Add(new SqlParameter("@Country", Country));
                    cmd.Parameters.Add(new SqlParameter("@Email", Email));
                    cmd.Parameters.Add(new SqlParameter("@Phone", Phone));
                    cmd.Parameters.Add(new SqlParameter("@Employees", Employees));
                    cmd.Connection = conn;
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        System.Windows.MessageBox.Show("Bedrijf succesvol toegevoegd!");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        public Boolean editCompany(int Id, String Name, String Street, int Zipcode, String City, String Country, String Email, int Phone, int Employees)
        {
            Boolean b = false;
            try
            {
                using (SqlConnection conn = new SqlConnection(connection))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("UPDATE Company SET Name=@Name, Street=@Street, Zipcode=@Zipcode, City=@City, Country=@Country, Email=@Email, Phone=@Phone, Employees=@Employees WHERE Id=@Id", conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add(new SqlParameter("@Id", Id));
                    cmd.Parameters.Add(new SqlParameter("@Name", Name));
                    cmd.Parameters.Add(new SqlParameter("@Street", Street));
                    cmd.Parameters.Add(new SqlParameter("@Zipcode", Zipcode));
                    cmd.Parameters.Add(new SqlParameter("@City", City));
                    cmd.Parameters.Add(new SqlParameter("@Country", Country));
                    cmd.Parameters.Add(new SqlParameter("@Email", Email));
                    cmd.Parameters.Add(new SqlParameter("@Phone", Phone));
                    cmd.Parameters.Add(new SqlParameter("@Employees", Employees));
                    cmd.Connection = conn;
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        System.Windows.MessageBox.Show("Bedrijf succesvol bewerkt!");
                        b = true;
                    }
                    else
                    {
                        b = false;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return b;
        }

        public void deleteCompany(int id)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connection))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("DELETE FROM Company WHERE Id = @id", conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add(new SqlParameter("@id", id));
                    cmd.Connection = conn;
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        System.Windows.MessageBox.Show("Bedrijf succesvol verwijderd!");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

            }
        }

        public Boolean checkIfContractCompany(int companyId)
        {
            Boolean b = false;
            try
            {
                using (SqlConnection conn = new SqlConnection(connection))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(@"SELECT [Id]
                                                      ,[StartDate]
                                                      ,[EndDate]
                                                      ,[CompanyId]
                                                      ,[ContractFormulaId]
                                                  FROM [Pj].[dbo].[Contract]
                                                  WHERE CompanyId=@id ", conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add(new SqlParameter("@id", companyId));
                    cmd.Connection = conn;
                    SqlDataReader reader = cmd.ExecuteReader();
                    reader.Read();
                    if(reader.HasRows)
                    {
                        b = false;
                        System.Windows.MessageBox.Show("Dit bedrijf is verbonden aan een contract.");
                        
                    }
                    else
                    {
                        b = true;
                    }
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return b;
        }

        public Boolean checkIfReservationContract(int contractId)
        {
            Boolean b = false;
            try
            {
                using (SqlConnection conn = new SqlConnection(connection))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(@"SELECT Reservation.[Id]
		                                            ,Company.Name
                                                  ,Reservation.[StartDate]
                                                  ,Reservation.[EndDate]
                                                  ,Reservation.[CompanyId]
                                                  ,Reservation.[LocationId]
                                                  ,Reservation.[CreateDate]
	                                              ,Contract.ContractFormulaId
                                              FROM [Pj].[dbo].[Reservation]
                                              INNER JOIN [Pj].[dbo].[Company] on Reservation.CompanyId = Company.Id
                                              INNER JOIN [Pj].[dbo].[Contract] on Company.Id = Contract.CompanyId
                                              WHERE Contract.Id = @id", conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add(new SqlParameter("@id", contractId));
                    cmd.Connection = conn;
                    SqlDataReader reader = cmd.ExecuteReader();
                    reader.Read();
                    if (reader.HasRows)
                    {
                        b = false;
                        System.Windows.MessageBox.Show("Er zijn reservaties verbonden aan dit contract.");

                    }
                    else
                    {
                        b = true;
                    }
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return b;
        }


        public void newContract(DateTime start, DateTime end, int companyId, int contractFormula)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connection))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("INSERT INTO Contract(StartDate, EndDate, CompanyId, ContractFormulaId) VALUES(@StartDate, @EndDate, @CompanyId, @ContractFormulaId)", conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add(new SqlParameter("@StartDate", start));
                    cmd.Parameters.Add(new SqlParameter("@EndDate", end));
                    cmd.Parameters.Add(new SqlParameter("@CompanyId", companyId));
                    cmd.Parameters.Add(new SqlParameter("@ContractFormulaId", contractFormula));
                    cmd.Connection = conn;
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        System.Windows.MessageBox.Show("Contract succesvol toegevoegd!");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

            }
        }

    }
}
